#!/bin/bash

#判断是否为空值
if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi


#SELECT 查询语句
DIM_ADS_PLATFORM_INFO_FULL_SQL="
WITH
    t1 AS (
        SELECT
            id, ad_id, platform_id, create_time, cancel_time
        FROM jtp_ads_warehouse.ods_ads_platform_full
        WHERE dt='${data_date}'
    )
    ,t2 AS (
        SELECT
            id, product_id, material_id, group_id AS ad_group_id, ad_name, materail_url
        FROM jtp_ads_warehouse.ods_ads_info_full
        WHERE dt='${data_date}'
    )
   ,t3 AS (
        SELECT
            id, name AS product_name, price AS product_price
        FROM jtp_ads_warehouse.ods_ads_product_full
        WHERE dt='${data_date}'
    )
    ,t4 AS (
        SELECT
            id, platform AS platform_name, platform_alias_zh AS platform_name_zh
        FROM jtp_ads_warehouse.ods_platform_info_full
        WHERE dt='${data_date}'
    )
INSERT OVERWRITE TABLE jtp_ads_warehouse.dim_ads_platform_info_full PARTITION (dt='${data_date}')
SELECT
    t1.id
    ,t1.ad_id
    ,t2.ad_name
    ,t2.ad_group_id
    ,t2.product_id
    ,t3.product_name
    ,t3.product_price
    ,t2.material_id
    ,t1.platform_id
    ,t4.platform_name
    ,t4.platform_name_zh
    ,t1.create_time
    ,t1.cancel_time
FROM t1
LEFT JOIN t2 ON t1.ad_id=t2.id
LEFT JOIN t3 ON t2.product_id=t3.id
LEFT JOIN t4 ON t1.platform_id=t4.id
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_ADS_PLATFORM_INFO_FULL_SQL}"

